﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using System.IO;
using importExcel;
using CubeDAL;

namespace ExcelProcess
{
    public class ExcelExtracter
    {
        private string s_path, sh_name, t_path;
        private int skip_line = 1;
        
        public ExcelExtracter(string source_path, string sheet_name, string target_path) {
            
            s_path = source_path;
            sh_name = sheet_name;
            t_path = target_path;
        }

        public int Execute() {
            DataSet content = LoadDataFromExcel(s_path, sh_name);
            StreamWriter sw = new StreamWriter(t_path, false, Encoding.UTF8);
            int count = 0;
            if (content.Tables == null)
                return count;
            foreach (DataTable table in content.Tables) {
                count=table.Rows.Count;
                foreach (DataRow row in table.Rows) {
                    if (skip_line > 0) {
                        --skip_line;
                    } else {
                        foreach (DataColumn col in table.Columns) {
                            sw.Write(row[col].ToString().Trim().Replace("\n", " ").Replace("\t", " ") + "\t");
                        }
                        sw.WriteLine();
                    }
                }
            }
            sw.Flush();
            sw.Close();
            return count;
        }

        private DataSet LoadDataFromExcel(string filePath, string sheetName)
        {
            try
            {
                /*OleDbConnection conn = new OleDbConnection(strConn);
                DataTable dt = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                string tableName = dt.Rows[0][2].ToString().Trim();*/
                string conn;
                conn = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                         "Data source=" + filePath + ";" +
                         "Extended Properties=Excel 8.0";
                OleDbConnection oleconn = new OleDbConnection(conn);
                
                oleconn.Open();
                string sql = "SELECT * FROM [" + sheetName + "$]";

                OleDbDataAdapter oleDataExcel = new OleDbDataAdapter(sql, oleconn);
                DataSet ds = new DataSet();
                oleDataExcel.Fill(ds, sheetName);
                oleconn.Close();
                return ds;

            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                return null;
            }
        }
    }
}
